package web.sanpham;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class connectDBSP {
	Connection connect = null;
	Statement stmt = null;
	ResultSet rs = null;
	/**
	 * Check Driver connect to MySQL
	 */
	protected void hasDriver() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException ex) {
			throw new Exception("Invalid Driver!!Please check this drver....");
		}
	}

	/**
	 * Function used to get the connection to the Database Step 1 - I check my
	 * connection or not!! Step 2 - If not, it will be null and initialization.
	 * Step 3 - Then it return
	 * 
	 * @return Connection
	 */
	protected Connection openConnect() throws Exception {
		if (connect == null) {
			hasDriver();
			String url = "jdbc:mysql://localhost/web";
			// url_sql = "....";
			try {
				this.connect = DriverManager
						.getConnection(url, "root", "12345");
			} catch (SQLException e) {
				throw new Exception(e.getMessage()
						+ "Connect failed to database .... ");
			}
		}
		return connect;
	}

	/**
	 * Make a Statement to execute the SQL statement
	 * 
	 * @return Statement
	 */
	protected Statement getStatement() throws SQLException, Exception {
		if (stmt == null) {
			stmt = openConnect().createStatement();
		}
		return stmt;
	}

	/**
	 * Used to execute the Select statement
	 * 
	 * @param strSQL
	 *            Query VD: Select * from Employee
	 * @return ResultSet
	 */
	// thuc hien cac cau lenh cho data sanpham
		public ArrayList<sanpham> getAllSanphams() throws Exception {
			ArrayList<sanpham> lst = new ArrayList<sanpham>();
			String strSQL = "select * from sanpham";
			try {
				rs = getStatement().executeQuery(strSQL);
				while (rs.next()) {
					String ms = rs.getString("masp");
					String ten = rs.getString("tensp");
					String nsx = rs.getString("nhasx");
					String thbh = rs.getString("thoihanbaohanh");
					String ttsp = rs.getString("thongtinsp");
					String cl= rs.getString("chatlieu");
					String cc=rs.getString("chieucao");
					sanpham sp = new sanpham(ms, ten, nsx,thbh, ttsp,cl,cc);
					lst.add(sp);
				}
			} catch (Exception e) {
				throw new Exception(e.getMessage() + " Error at : " + strSQL);
			}
			closeConnet();
			return lst;
		}

		public boolean insertNew(sanpham sp) throws Exception {
			String sql = "insert into sanpham values(?,?,?,?,?,?,?)";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			pst.setString(1, sp.getMasp());
			pst.setString(2, sp.getTensp());
			pst.setString(2, sp.getNhasx());
			pst.setString(4, sp.getThoihanbaohanh());
			pst.setString(5, sp.getThongtinsp());
			pst.setString(6, sp.getChatlieu());
			pst.setString(7, sp.getChieucao());
			return pst.executeUpdate() > 0;

		}

		public boolean deleteSP(String masp) throws Exception {
			String sql = "delete from sanpham where masp=?";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			pst.setString(1, masp);
			return pst.executeUpdate() > 0;
		}

		/**
		 * Used to execute the Insert, Update, Delete statement
		 * 
		 * @param strSQL
		 *            Query VD: Insert into TableName values ('??','??')
		 * @return The number of lines affected by the command
		 */
		public int executeUpdate(String strSQL) throws Exception {
			int result = 0;
			try {
				result = getStatement().executeUpdate(strSQL);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage() + " Error at: " + strSQL);
			} finally {
				this.closeConnet();
			}
			return result;
		}

		public sanpham getSanPhamByID(String masp) throws Exception {
			String sql = "select * from sanpham where masp=?";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			pst.setString(1, masp);
			ResultSet rs = pst.executeQuery();
			sanpham sp = null;
			if (rs.next()) {
				String ms = rs.getString("masp");
				String ten = rs.getString("tensp");
				String nsx = rs.getString("nhasx");
				String thbh = rs.getString("thoihanbaohanh");
				String ttsp = rs.getString("thongtinsp");
				String cl= rs.getString("chatlieu");
				String cc=rs.getString("chieucao");
				 sp = new sanpham(ms, ten, nsx,thbh, ttsp,cl,cc);
			}
			return sp;
		}

		public boolean UpdateSanPham(String masp, sanpham newsp) throws Exception {
			String sql = "update SanPham set tensp=?, nhasx=?,thoihanbaohanh=?, thongtinsp=? chatlieu=?,chieucao=? where masp=?";
			PreparedStatement pst = openConnect().prepareStatement(sql);
			
			pst.setString(1, newsp.getTensp());
			pst.setString(2, newsp.getNhasx());
			pst.setString(3, newsp.getThoihanbaohanh());
			pst.setString(4, newsp.getThongtinsp());
			pst.setString(5, newsp.getChatlieu());
			pst.setString(6, newsp.getChieucao());
			pst.setString(7, newsp.getMasp());
			return pst.executeUpdate() > 0;
		}

		
		/**
		 * A method to close the connection.
		 * 
		 * @throws SQLException
		 */
		public void closeConnet() throws SQLException {
			if (rs != null && !rs.isClosed())
				rs.close();
			if (stmt != null)
				stmt.close();
			if (connect != null)
				connect.close();
		}

}
